package dao.entity;

import bean.Customer;
import dao.MysqlConfiguration;
import dao.interf.CustomerDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl_2 implements CustomerDao {

    @Override
    public List<Customer> customer_find(int currentPage) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from customers limit ?,?;";
        ResultSet rs;
        List<Customer> list = new ArrayList<Customer>();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,(currentPage-1)*5);
            pst.setInt(2,5);
            rs = pst.executeQuery();
            while (rs.next()){
                Customer c = new Customer();
                c.setCid(rs.getString(1));
                c.setCname(rs.getString(2));
                c.setPwd(rs.getString(3));
                c.setGender(rs.getString(4));
                c.setC_email(rs.getString(5));
                c.setC_phone(rs.getString(6));
                c.setLg_time(rs.getString(7));
                c.setC_status(rs.getString(8));
                list.add(c);//添加当前顾客用户到列表中
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return list;
    }

    @Override
    public int addCustomer(Customer c) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "insert into customers values(?,?,?,?,?,?,?,?)";

        int n = 0;//受影响的行数
        try {
            //创建statement语句对象
            //Statement st = conn.createStatement();
            //创建一个PrepareStatement语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功!");
            pst.setString(1,c.getCid());
            pst.setString(2,c.getCname());
            pst.setString(3,c.getPwd());
            pst.setString(4,c.getGender());
            pst.setString(5,c.getC_email());
            pst.setString(6,c.getC_phone());
            pst.setString(7,c.getLg_time());
            pst.setString(8,c.getC_status());
            n = pst.executeUpdate();//执行语句
            System.out.println("执行语句成功!");
            //执行指定的sql语句
            //n = pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return n;//返回受影响的行数
    }

    @Override
    public int customer_count() {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select count(*) from customers;";
        ResultSet rs;
        int n = 0;
        try {
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                n = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败! 错误是:"+e.getMessage());
        }
        return n;
    }

    @Override
    public int deleteCustomer(String cid) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "delete from customers where cid=?";
        int n = 0;//初始化受影响的行数
        try {
            //定义预处理语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功");
            pst.setString(1,cid);
            //pst.setInt(1,sno);
            n = pst.executeUpdate();//执行语句
            //System.out.println("执行语句成功");
        } catch (Exception e) {
            System.out.println("创建语句异常: "+e.getMessage());
        }
        return n;
    }

    @Override
    public int selectCidmaxCustomer() {
        //连接数据库
        String cid = "";
        int num = 0;
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select  max(cid) from customers;";
        ResultSet rs = null;
        try {
            Statement st = conn.createStatement();
            System.out.println("查询评论语句创建成功!");
            rs = st.executeQuery(sql);
            if(rs!=null){
                System.out.println("查找成功");
                rs.next();//移动光标
                System.out.println("光标移动成功");
                cid = rs.getString(1);
                System.out.println("查询成功,cid为"+cid);
                cid = cid.substring(1);
                num = Integer.parseInt(cid);
                System.out.println("获取成功,cid序号为"+ num);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return num;
    }

    @Override
    public ResultSet queryCustomerByCid(String cid) {
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from customers where cid=?;";
        ResultSet rs = null;
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1,cid);
            rs = pst.executeQuery();
        } catch (Exception e) {
            System.out.println("错误!"+e.getMessage());
        }
        return rs;
    }
}
